/*
--| This section is commented out to prevent accidental deletion of existing stored procedure's.
--| This can be uncommented after the stored procedure is created and confirmed that another stored procedure
--| with the same name does not exists.
--|
--| Drop stored procedure if it already exists
IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'dbo'
     AND SPECIFIC_NAME = N'au_getGroupTypesEnum' 
)
   DROP PROCEDURE dbo.au_getGroupTypesEnum
GO

*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--|--------------------------------------------------------------------------------------------------------------
--|Name:			au_getGroupTypesEnum
--|Author:			Jamie Swindall
--|Description:		Returns an enumeration of viewable Group Types by Program.
--|-------------------------------------------------------------------------------------------------------------- 
--|------------- Parameters -------------------------------------------------------------------------------------
--|--------------------------------------------------------------------------------------------------------------
--| Name		||	Data Type	||	Default Value	||	Description
--|--------------------------------------------------------------------------------------------------------------
--|@ProgramId || INT || 0 ||
--|
--|--------------------------------------------------------------------------------------------------------------
--|--------------------------- Revision Information	-------------------------------------------------------------				 
--|--------------------------------------------------------------------------------------------------------------
--|	Author:				Date			Change					Description							SCR #
--|--------------------------------------------------------------------------------------------------------------
--|	Jamie Swindall			Initial version						
--|														
--|--------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE dbo.au_getGroupTypesEnum
	@ProgramId INT = 0
AS
BEGIN
	SET NOCOUNT ON;

    IF @ProgramId = 0 
		BEGIN
			SELECT	[dbo].[au_GroupType].[Id], [dbo].[au_GroupType].[Description]
			FROM	[dbo].[au_GroupType]
			WHERE	[dbo].[au_GroupType].[IsViewable] = 1
		END
	ELSE
		BEGIN
			SELECT	[dbo].[au_GroupType].[Id], [dbo].[au_GroupType].[Description]
			FROM	[dbo].[au_GroupType]
			WHERE	[dbo].[au_GroupType].[ProgramId] = @ProgramId
			AND		[dbo].[au_GroupType].[IsViewable] = 1
		END

END
GO

/*
-- =============================================
-- Example to execute the stored procedure
-- =============================================
EXECUTE dbo.au_getGroupTypesEnum
	
GO
*/